"""
Copyright 2012 Wieser Dietmar, Margit Mutschlechner.

This file is part of Multimedia Social Networking Platform.

Multimedia Social Networking Platform is free software: 
you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Multimedia Social Networking Platform is distributed
in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Multimedia Social Networking Platform.  
If not, see <http://www.gnu.org/licenses/>.

"""

import traceback
from msnp.logger import Logger
from msnp.model import MediaModel
from msnp.service.utils import StringCompare
from msnp.persistence.mysql import DBErrorHandler

########
#tables#
########
TABLE_ARTIST = 'artist'
TABLE_AUDIO = 'audio'
TABLE_MEDIA = 'media'
TABLE_GENRE = 'genre'
TABLE_COLLECTION = 'collection'
TABLE_RATING = 'rating'
TABLE_SYNCH_CHANGES = 'synch_changes'

##########################
#columns for artist table#
##########################
COL_ART_ID = 'id'
COL_ART_SELECT_ID = 'artist_id'
COL_ART_NAME = 'name'
COL_ART_SELECT_NAME = 'artist_name'

##############################
#columns for collection table#
##############################
COL_COLLECTION_ID = 'id'
COL_COLLECTION_SELECT_ID = 'collection_id'
COL_COLLECTION_NAME = 'name'
COL_COLLECTION_SELECT_NAME = 'collection_name'

#########################
#columns for audio table#
#########################
COL_AUDIO_ID = 'id'
COL_AUDIO_SELECT_ID = 'audio_id'
COL_AUDIO_MEDIA_ID = 'media_id'
COL_AUDIO_ARTIST_ID = 'artist_id'
COL_AUDIO_LYRICS = 'lyrics'
COL_AUDIO_TRACKNUMBER = 'tracknumber'
COL_AUDIO_DISCNUMBER = 'discnumber'
COL_AUDIO_BITRATE = 'bitrate'
COL_AUDIO_BPM = 'bpm'

#########################
#columns for genre table#
#########################
COL_GENRE_ID = 'id'
COL_GENRE_SELECT_ID = 'genre_id'
COL_GENRE_NAME = 'name'
COL_GENRE_SELECT_NAME = 'genre_name'

##############################
#columns for genre_type table#
##############################
COL_GT_ID = 'id'
COL_GT_NAME = 'name'

#########################
#columns for media table#
#########################
COL_MEDIA_ID = 'id'
COL_MEDIA_SELECT_ID = 'media_id'
COL_MEDIA_TYPE = 'media_type'
COL_MEDIA_TITLE = 'title'
COL_MEDIA_YEAR = 'year'
COL_MEDIA_COMPOSER = 'composer'
COL_MEDIA_COLLECTION_ID = 'collection_id'
COL_MEDIA_GENRE_ID = 'genre_id'
COL_MEDIA_LENGTH = 'length'
COL_MEDIA_FILESIZE = 'filesize'
COL_MEDIA_FILETYPE = 'filetype'

##########################
#columns for rating table#
##########################
COL_RATING_ID = 'id'
COL_RATING_MEDIA_ID = 'media_id'
COL_RATING_USERNAME = 'username'
COL_RATING_SCORE = 'score'
COL_RATING_RATING = 'rating'
COL_RATING_PLAYCOUNT = 'playcount'
COL_RATING_COMMENT = 'comment'


def dropTablesAndSchema(database, cursor, schema):
    """Drops the given schema.
    
    Args:
        database: A Connection object representing the database where the tables should be dropped.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String representing the schema that should be dropped.
    """
    try:
        query = "drop schema if exists " + schema
        cursor.execute(query)
        database.commit()
    except:
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.DROP, traceback.format_exc())

def existsSchemaAndTables(database, cursor, schema):
    """Check whether there exists a media database with the given schema and the correct tables.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        
    Returns:
        True is returned, if there exists a media database with the given schema and the correct tables, False otherwise.
    """
    try:
        query = "select table_name from information_schema.tables where table_schema = '" + schema + "' order by table_name asc"
        Logger.mediaDBinfo(query)
        cursor.execute(query)
        
        tables = ["artist", "audio", "collection", "genre", "last_netsynch", "media", "net_synch_changes", "rating", "synch_changes"]
        
        for table in tables:
            result = cursor.fetchone()
            if result == None:
                Logger.info("table " + table + " does not exist in media db")
                return False
            else:
                if result[0] != table:
                    Logger.info("table " + table + " does not exist in media db")
                    return False
        
        query = "select count(*) from " + schema + "." + TABLE_MEDIA
        cursor.execute(query)
        result = cursor.fetchone()
        # media table is empty
        if result == None or result[0] == "0":
            Logger.info("no media in media db")
            return False
        
        return True
    except:
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.CHECK, traceback.format_exc())
    return False
    
def executeQuery(database, cursor, query):
    """Executes the given query on the given database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        query: A String containing the query to be executed.
    """
    try:
        cursor.execute(query)
        database.commit()
    except:
        Logger.info("error while executing query: " + query)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UNKNOWN, traceback.format_exc())
        
############
## SELECT ##
############
        
def selectArtistForName(database, cursor, schema, artistName):
    """Selects from the media database the artist with the given name.
    
    All artists are selected and matched with the given name (not exact comparison).
    The first that is similar to the given name is returned.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        artistName: A String representing the searched artist's name.
    
    Returns:
        An Artist object containing the searched artist is returned, or None if an error occurred or no such artist exists.
    """
    artists = __selectAllArtists(database, cursor, schema)
    if artists != None:
        for artist in artists:
            if StringCompare.isEquals(artist.getName(), artistName):
                return artist
    return None

def selectGenreForName(database, cursor, schema, genreName):
    """Selects from the media database the genre with the given name.
    
    All genres are selected and matched with the given name (not exact comparison).
    The first one, that is similar to the given name is returned.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        genreName: A String representing the searched genre's name.
    
    Returns:
        A Genre object containing the searched genre is returned, or None if an error occurred or no such genre exists.
    """
    genres = __selectAllGenres(database, cursor, schema)
    if genres != None:
        for genre in genres:
            if StringCompare.isEquals(genre.getName(), genreName):
                return genre
    return None

def selectAudiosForMediaIds(database, cursor, schema, mediaIds, ratingForUser):
    """Selects from the media database the audios corresponding to the given ids.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        mediaIds: A list of integers containing the searched ids.
        ratingForUser: A list of Strings containing user names for which the rating of the Audio object should be selected and added.
            If None, then all existing ratings for the Audio object are retrieved.
    
    Returns:
        A list of Audio objects containing the searched audios is returned or None if an error occurred or no such audio exists.
    """
    if mediaIds != None and len(mediaIds) > 0:
        query = __getQuerySelectAudiosForMediaIds(schema, mediaIds)
        audios = __selectAudiosFromQuery(database, cursor, schema, query, ratingForUser)
        return audios
    return None

def selectAudiosForTitleArtistAndCollection(database, cursor, schema, title, artistName, collectionName):
    """Selects from the media database the audios corresponding to the given title, artist name and collection name.
   
    All audios are selected and matched with the given values (not exact comparison).
    All audios, that are similar to the given values are returned.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        title: A String containing the title of the searched audio. If None this field is omitted during the matching.
        artistName: A String containing the artist name of the searched audio.
        collectionName: A String containing the collection name of the searched audio. If None this field is omitted during the matching.
    
    Returns:
        A list of Audio objects containing the searched audios is returned.
    """
    audios = selectAllAudios(database, cursor, schema)
    audiosForTitleArtistAndCollection = []
    if audios != None:
        for audio in audios:
            isTitleEquals = False
            if title != None and len(title) > 0:
                isTitleEquals = StringCompare.isEquals(audio.getMedia().getTitle(), title)
            isArtistEquals = StringCompare.isEquals(audio.getArtist().getName(), artistName)
            isCollectionEquals = True
            if audio.getMedia().getCollection() != None and audio.getMedia().getCollection().getName() != None and len(audio.getMedia().getCollection().getName()) > 0 and collectionName != None and len(collectionName) > 0:
                isCollectionEquals = StringCompare.isEquals(audio.getMedia().getCollection().getName(), collectionName)
            if isTitleEquals and isArtistEquals and isCollectionEquals:
                audiosForTitleArtistAndCollection.append(audio)
    if len(audiosForTitleArtistAndCollection) == 0:
        audiosForTitleArtistAndCollection = None
    return audiosForTitleArtistAndCollection


def selectAudiosForGenre(database, cursor, schema, genreName):
    """Selects from the media database the audios corresponding to the given genre name.
    
    All audios are selected and matched with the given value (not exact comparison).
    All audios, that are similar to the given value, are returned.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        genreName: A String containing the genre name of the searched audio.
    
    Returns:
        A list of Audio objects containing the searched audios is returned.
    """
    audios = selectAllAudios(database, cursor, schema)
    audiosForGenre = []
    if genreName != None:
        if audios != None:
            for audio in audios:
                if audio.getMedia().getGenre() != None and audio.getMedia().getGenre().getName() != None:
                    isGenreEquals = StringCompare.isEquals(audio.getMedia().getGenre().getName(), genreName)
                    if isGenreEquals:
                        audiosForGenre.append(audio)
    if len(audiosForGenre) == 0:
        audiosForGenre = None
    return audiosForGenre


def selectCollectionForNameAndArtist(database, cursor, schema, collectionName, artistName):
    """Selects from the media database the collection with the given name and from the given artist.
    
    All audios are selected. Artist and collection of each audio are matched with the given ones (not exact comparison).
    The collection that is similar to the given values is returned.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        collectionName: A Sring representing the searched collection.
        artistName: A String representing the name of the collection's artist.
    
    Returns:
        A Collection object containing the searched artist is returned, or None if an error occurred or no such collection exists.
    """
    audios = selectAllAudios(database, cursor, schema)
    if audios != None:
        for audio in audios:
            isArtistEquals = StringCompare.isEquals(audio.getArtist().getName(), artistName)
            if audio.getMedia().getCollection() != None and audio.getMedia().getCollection().getName() != None and isArtistEquals:
                isCollectionEquals = StringCompare.isEquals(audio.getMedia().getCollection().getName(), collectionName)
                if isCollectionEquals:
                    return audio.getMedia().getCollection()
    return None


def selectRatingForMediaIdAndUsername(database, cursor, schema, mediaId, username):
    """Selects from the media database the rating of the given user for the given media.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        mediaid: An integer containing the id of the media, for which the rating should be retrieved.
        username: A String containing the user name, for which the rating should be retrieved.
    
    Returns:
        A list of Rating objects is returned, or None if an error occurred or no such rating exists.
    """
    ratings = __selectAllRatingsForMediaId(database, cursor, schema, mediaId)
    if ratings != None:
        for rating in ratings:
            if rating.getUsername() == username:
                return rating
    return None

def __selectAllArtists(database, cursor, schema):
    """Selects from the media database all artists.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A list of Artist objects containing all artist is returned, or None if an error occurred.
    """
    try:
        query = __getQuerySelectAllArtists(schema)
        #Logger.info(query)
        cursor.execute(query)
        artists = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            id = int(result[0])
            name = result[1]
            a = MediaModel.Artist.createArtist(name, id)
            artists.append(a)
        return artists
    except:
        Logger.info("Error: unable to select all artists")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def __selectAllGenres(database, cursor, schema):
    """Selects from the media database all genres.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A list of Genre objects containing all genres is returned, or None if an error occurred.
    """
    try:
        query = __getQuerySelectAllGenres(schema)
        #Logger.info(query)
        cursor.execute(query)
        genres = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            id = int(result[0])
            name = result[1]
            g = MediaModel.Genre.createGenre(name, id)
            genres.append(g)
        return genres
    except:
        Logger.info("Error: unable to select all genres")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def selectAllAudios(database, cursor, schema, audioId=None):
    """Selects from the media database all audios.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        audioId: An integer value containing the id of the searched audio. If None all audios are selected.
        
    Returns:
        A list of all Audio objects is returned.
    """
    query = __getQuerySelectAllAudios(schema, audioId)
    audios = __selectAudiosFromQuery(database, cursor, schema, query)
    return audios

def __selectAudiosFromQuery(database, cursor, schema, query, ratingForUser=None):
    """Selects from the media database the audios retrieved by executing the given query.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        ratingForUser: A list of Strings containing user names for which the rating of the Audio object should be selected and added.
            If None, then all existing ratings for the Audio object are retrieved. Default None.
    
    Returns:
        A list of Audio objects containing the searched audios is returned.
    """
    try:
#        print query
        #Logger.info(query)
        cursor.execute(query)
        audios = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            audioId = -1
            if result[0] != None:
                audioId = int(result[0])
            lyrics = result[1]
            tracknumber = None
            if result[2] != None:
                tracknumber = int(result[2])
            discnumber = None
            if result[3] != None:
                discnumber = int(result[3])
            bitrate = None
            if result[4] != None:
                bitrate = int(result[4])
            bpm = None
            if result[5] != None:
                bpm = int(result[5])
            mediaId = None
            if result[6] != None:
                mediaId = int(result[6])
            mediaType = result[7]
            title = result[8]
            year = result[9]
            composer = result[10]
            length = None
            if result[11] != None:
                length = int(result[11])
            filetype = result[12]
            filesize = None
            if result[13] != None:
                filesize = int(result[13])
            artistId = None
            if result[14] != None:
                artistId = int(result[14])
            artistName = result[15]
            genreId = None
            if result[16] != None:
                genreId = int(result[16])
            genreName = result[17]
            collectionId = None
            if result[18] != None:
                collectionId = int(result[18])
            collectionName = result[19]
            a = MediaModel.Audio.createAudioFromMediaDb(audioId, lyrics, tracknumber, discnumber, bitrate, bpm, mediaId, mediaType, title, year, composer, length, filetype, filesize, artistId, artistName, genreId, genreName, collectionId, collectionName)
            audios.append(a)
        #add the ratings
        for audio in audios:
            mediaId = audio.getMedia().getId()
            if mediaId != None:
                ratings = __selectAllRatingsForMediaId(database, cursor, schema, mediaId, ratingForUser)
                audio.getMedia().setRatings(ratings)
        return audios
    except:
        Logger.info("Error: unable to select audios")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def __selectAllRatingsForMediaId(database, cursor, schema, mediaId, ratingForUser=None):
    """Selects from the media database the rating of the given user for the given media.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        mediaid: An integer containing the id of the media, for which the rating should be retrieved.
        ratingForUser: A list of Strings containing the user names, for which the rating should be retrieved.
            If None the rating of all users are retrieved. Default None.
    
    Returns:
        A list of Rating objects is returned.
    """
    try:
        if ratingForUser != None and len(ratingForUser) > 0:
            query = __getQuerySelectAllRatingsForMediaIdAndUser(schema, mediaId)
            cursor.execute(query, [ratingForUser])
        else:
            query = __getQuerySelectAllRatingsForMediaId(schema, mediaId)
            cursor.execute(query)
        ratings = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            id = int(result[0])
            mediaId = None
            if result[1] != None:
                mediaId = int(result[1])
            username = result[2]
            score = None
            if result[3] != None:
                score = float(result[3])
            rating = None
            if result[4] != None:
                rating = int(result[4])
            playcount = None
            if result[5] != None:
                playcount = int(result[5])
            comment = result[6]
            r = MediaModel.Rating.createRating(score, rating, playcount, comment, id, username, mediaId)
            ratings.append(r)
        return ratings
    except:
        Logger.info("Error: unable to select all ratings")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None
    

############
## INSERT ##
############

def insertArtist(database, cursor, schema, artist):
    """Inserts the given artist into the media database.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        artist: An Artist object containing the artist to insert.
    
    Returns:
        The id of the inserted artist is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        if artist != None:
            name = artist.getName()
            query = __getQueryInsertArtist(schema)
            #Logger.info(query)
            cursor.execute(query, [name])
            database.commit()
            insertId = cursor.lastrowid
        else:
            Logger.info("Error - MediaAccessor: artist is None in insertArtist")
    except:
        Logger.info("Error: unable to insert artist")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return insertId

def insertCollection(database, cursor, schema, collection):
    """Inserts the given collection into the media database.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        collection: A Collection object containing the collection to insert.
    
    Returns:
        The id of the inserted collection is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        if collection != None:
            name = collection.getName()
            query = __getQueryInsertCollection(schema)
            #Logger.info(query)
            cursor.execute(query, [name])
            database.commit()
            insertId = cursor.lastrowid
        else:
            Logger.info("Error - MediaAccessor: collection is None in insertCollection")
    except:
        Logger.info("Error: unable to insert collection")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return insertId

def insertGenre(database, cursor, schema, genre):
    """Inserts the given genre into the media database.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        genre: A Genre object containing the genre to insert.
    
    Returns:
        The id of the inserted genre is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        if genre != None:
            name = genre.getName()
            query = __getQueryInsertGenre(schema)
            #Logger.info(query)
            cursor.execute(query, [name])
            database.commit()
            insertId = cursor.lastrowid
        else:
            Logger.info("Error - MediaAccessor: genre is None in insertgenre")
    except:
        Logger.info("Error: unable to insert genre")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return insertId

def insertRating(database, cursor, schema, ratingObj, mediaId):
    """Inserts the given rating for the given media id into the media database.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        ratingObj: A Rating object containing the rating to insert.
        mediaId: An integer representing the id of the media for which the rating should be inserted.
    
    Returns:
        The id of the inserted rating is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        if ratingObj != None:
            username = ratingObj.getUsername()
            score = ratingObj.getScore()
            rating = ratingObj.getRating()
            playcount = ratingObj.getPlaycount()
            comment = ratingObj.getComment()
            if mediaId == None:
                mediaId = -1
            if score == None:
                score = 0
            if rating == None:
                rating = 0
            if playcount == None:
                playcount = 0
            query = __getQueryInsertRating(schema)
            #Logger.info(query)
            cursor.execute(query, [mediaId, username, score, rating, playcount, comment])
            database.commit()
            insertId = cursor.lastrowid
        else:
            Logger.info("Error - MediaAccessor: rating is None in insertrating")
    except:
        Logger.info("Error: unable to insert rating")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return insertId

def insertAudio(database, cursor, schema, audio):
    """Inserts the given audio into the media database.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        audio: An Audio object containing the audio to insert.
    
    Returns:
        The id of the inserted audio is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        if audio != None:
            mediaId = audio.getMedia().getId()
            artistId = audio.getArtist().getId()
            lyrics = audio.getLyrics()
            tracknumber = audio.getTracknumber()
            discnumber = audio.getDiscnumber()
            bitrate = audio.getBitrate()
            bpm = audio.getBPM()
            if mediaId == None:
                mediaId = -1
            if artistId == None:
                artistId = -1
            query = __getQueryInsertAudio(schema)
            #Logger.info(query)
            cursor.execute(query, [mediaId, artistId, lyrics, tracknumber, discnumber, bitrate, bpm])
            database.commit()
            insertId = cursor.lastrowid
        else:
            Logger.info("Error - MediaAccessor: audio is None in insertaudio")
    except:
        Logger.info("Error: unable to insert audio")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return insertId

def insertMedia(database, cursor, schema, media):
    """Inserts the given media into the media database.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        media: A Media object containing the media to insert.
    
    Returns:
        The id of the inserted media is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        if media != None:
            mediaType = media.getMediaType()
            title = media.getTitle()
            year = media.getYear()
            composer = media.getComposer()
            collectionId = None
            if media.getCollection() != None:
                collectionId = media.getCollection().getId()
            genreId = None
            if media.getGenre() != None:
                genreId = media.getGenre().getId()
            length = media.getLength()
            filesize = media.getFilesize()
            filetype = ""
            query = __getQueryInsertMedia(schema)
            #Logger.info(query)
            cursor.execute(query, [mediaType, title, year, composer, collectionId, genreId, length, filesize, filetype])
            database.commit()
            insertId = cursor.lastrowid
        else:
            Logger.info("Error - MediaAccessor: media is None in insertmedia")
    except:
        Logger.info("Error: unable to insert media")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return insertId

############
## UPDATE ##
############

def updateArtist(database, cursor, schema, oldArtist, newArtist):
    """Updates the given oldArtist stored in the media database with the values of the given newArtist.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        oldArtist: An Artist object containing the valued of the old artist stored in the media database.
        newArtist: An Artist object containing the new values of the artist.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    try:
        if oldArtist != None and newArtist != None:
            if oldArtist.getName() != newArtist.getName():
                Logger.info("update artist (" + str(oldArtist.getId()) + "):")
                if oldArtist.getName() != None and newArtist.getName() != None:
                    Logger.info(" - name from " + oldArtist.getName() + " to " + newArtist.getName())
                else:
                    Logger.info(" - name -> one was None")
                name = newArtist.getName()
                query = __getQueryUpdateArtist(schema)
                #Logger.info(query)
                cursor.execute(query, [name, oldArtist.getId()])
                database.commit()
                return MediaModel.ReturnType.UPDATED
            else:
                Logger.info("no need to update artist")
                return MediaModel.ReturnType.NOT_UPDATED
        else:
            Logger.info("Error - MediaAccessor: artist is None in updateartist")
    except:
        Logger.info("Error: unable to update artist")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    return MediaModel.ReturnType.ERROR

def updateCollection(database, cursor, schema, oldCollection, newCollection):
    """Updates the given oldCollection stored in the media database with the values of the given newCollection.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        oldCollection: A Collection object containing the valued of the old collection stored in the media database.
        newCollection: A Collection object containing the new values of the collection.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    try:
        if oldCollection != None and newCollection != None:
            if oldCollection.getName() != newCollection.getName():
                Logger.info("update collection (" + str(oldCollection.getId()) + "):")
                if oldCollection.getName() != None and newCollection.getName() != None:
                    Logger.info(" - name from " + oldCollection.getName() + " to " + newCollection.getName())
                else:
                    Logger.info(" - name -> one was None")
                name = newCollection.getName()
                query = __getQueryUpdateCollection(schema)
                #Logger.info(query)
                cursor.execute(query, [name, oldCollection.getId()])
                database.commit()
                return MediaModel.ReturnType.UPDATED
            else:
                Logger.info(" - no need to update collection")
                return MediaModel.ReturnType.NOT_UPDATED
        else:
            Logger.info("Error - MediaAccessor: collection is None in updatecollection")
    except:
        Logger.info("Error: unable to update collection")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    return MediaModel.ReturnType.ERROR

#TODO depricated
def updateGenre(database, cursor, schema, oldGenre, newGenre):
    """Updates the given oldGenre stored in the media database with the values of the given newGenre.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        oldGenre: A Genre object containing the valued of the old genre stored in the media database.
        newGenre: A Genre object containing the new values of the genre.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    try:
        if oldGenre != None and newGenre != None:
            if oldGenre.getName() != newGenre.getName():
                name = newGenre.getName()
                query = __getQueryUpdateGenre(schema)
                #Logger.info(query)
                cursor.execute(query, [name, oldGenre.getId()])
                database.commit()
                return MediaModel.ReturnType.UPDATED
            else:
                Logger.info(" - no need to update genre")
                return MediaModel.ReturnType.NOT_UPDATED
        else:
            Logger.info("Error - MediaAccessor: genre is None in updategenre")
    except:
        Logger.info("Error: unable to update genre")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    return MediaModel.ReturnType.ERROR

def updateRating(database, cursor, schema, oldRating, newRating):
    """Updates the given oldRating stored in the media database with the values of the given newRating.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        oldRating: A Rating object containing the valued of the old rating stored in the media database.
        newRating: A Rating object containing the new values of the rating.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    try:
        if oldRating != None and newRating != None:
            Logger.info("update rating (" + newRating.getUsername() + "):")
            score = oldRating.getScore()
            rating = oldRating.getRating()
            playcount = oldRating.getPlaycount()
            comment = oldRating.getComment()
            updScore = oldRating.getScore() != newRating.getScore()
            if updScore:
                score = newRating.getScore()
                if oldRating.getScore() != None and newRating.getScore() != None:
                    Logger.info(" - score from " + str(oldRating.getScore()) + " to " + str(newRating.getScore()))
                else:
                    Logger.info(" - score -> one was None")
            updRating = oldRating.getRating() != newRating.getRating()
            if updRating:
                rating = newRating.getRating()
                if oldRating.getRating() != None and newRating.getRating() != None:
                    Logger.info(" - rating from " + str(oldRating.getRating()) + " to " + str(newRating.getRating()))
                else:
                    Logger.info(" - rating -> one was None")
            updPlaycount = oldRating.getPlaycount() != newRating.getPlaycount()
            if updPlaycount:
                playcount = newRating.getPlaycount()
                if oldRating.getPlaycount() != None and newRating.getPlaycount() != None:
                    Logger.info(" - playcount from " + str(oldRating.getPlaycount()) + " to " + str(newRating.getPlaycount()))
                else:
                    Logger.info(" - playcount -> one was None")
            updComment = oldRating.getComment() != newRating.getComment()
            if updComment:
                comment = newRating.getComment()
                if oldRating.getComment() != None and newRating.getComment() != None:
                    Logger.info(" - comment from " + oldRating.getComment() + " to " + newRating.getComment())
                else:
                    Logger.info(" - comment -> one was None")
            if updScore or updRating or updPlaycount or updComment:
                if score == None:
                    score = 0
                if rating == None:
                    rating = 0
                if playcount == None:
                    playcount = 0
                query = __getQueryUpdateRating(schema)
                #Logger.info(query)
                cursor.execute(query, [score, rating, playcount, comment, oldRating.getId()])
                database.commit()
                return MediaModel.ReturnType.UPDATED
            else:
                Logger.info(" - no need to update rating")
                return MediaModel.ReturnType.NOT_UPDATED
        else:
            Logger.info("Error - MediaAccessor: rating is None in updaterating")
    except:
        Logger.info("Error: unable to update rating")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    return MediaModel.ReturnType.ERROR

def updateMedia(database, cursor, schema, oldMedia, newMedia):
    """Updates the given oldMedia stored in the media database with the values of the given newMedia.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        oldMedia: A Media object containing the valued of the old media stored in the media database.
        newMedia: A Media object containing the new values of the media.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    try:
        if oldMedia != None and newMedia != None:
            Logger.info("update media (" + str(oldMedia.getId()) + "):")
            #default = old
            year = oldMedia.getYear()
            collectionId = None
            if oldMedia.getCollection() != None:
                collectionId = oldMedia.getCollection().getId()
                genreId = None
            if oldMedia.getGenre() != None:
                genreId = oldMedia.getGenre().getId()
                length = oldMedia.getLength()
            filesize = oldMedia.getFilesize()
            composer = oldMedia.getComposer()
            #check what to update and set right values
            updYear = oldMedia.getYear() != newMedia.getYear()
            if updYear:
                year = newMedia.getYear()
                if oldMedia.getYear() != None and newMedia.getYear() != None:
                    Logger.info(" - year from " + oldMedia.getYear() + " to " + newMedia.getYear())
                else:
                    Logger.info(" - year -> one was None")
            updCollectionId = False
            if newMedia.getCollection() != None and newMedia.getCollection().getId() != collectionId:
                updCollectionId = True
                collectionId = newMedia.getCollection().getId()
                if oldMedia.getCollection().getId() != None and newMedia.getCollection().getId() != None:
                    Logger.info(" - collectionid from " + str(oldMedia.getCollection().getId()) + " to " + str(newMedia.getCollection().getId()))
                else:
                    Logger.info(" - collectionid -> one was None")
            updGenreId = False
            if newMedia.getGenre() != None and newMedia.getGenre().getId() != genreId:
                updGenreId = True
                genreId = newMedia.getGenre().getId()
                if oldMedia.getGenre().getId() != None and newMedia.getGenre().getId() != None:
                    Logger.info(" - genreid from " + str(oldMedia.getGenre().getId()) + " to " + str(newMedia.getGenre().getId()))
                else:
                    Logger.info(" - genreid -> one was None")
            updLength = oldMedia.getLength() != newMedia.getLength()
            if updLength:
                length = newMedia.getLength()
                if oldMedia.getLength()!=None and newMedia.getLength()!=None:
                    Logger.info(" - length from " + str(oldMedia.getLength()) + " to " + str(newMedia.getLength()))
                else:
                    Logger.info(" - length -> one was None")
            updFilesize = oldMedia.getFilesize() != newMedia.getFilesize()
            if updFilesize:
                filesize = newMedia.getFilesize()
                if oldMedia.getFilesize()!=None and newMedia.getFilesize()!=None:
                    Logger.info(" - filesize from " + str(oldMedia.getFilesize()) + " to " + str(newMedia.getFilesize()))
                else:
                    Logger.info(" - filesize -> one was None")
            updComposer = oldMedia.getComposer() != newMedia.getComposer()
            if updComposer:
                composer = newMedia.getComposer()
                if oldMedia.getComposer()!=None and newMedia.getComposer()!=None:
                    Logger.info(" - composer from " + oldMedia.getComposer() + " to " + newMedia.getComposer())
                else:
                    Logger.info(" - composer -> one was None")
            #filetype ignored
            if updYear or updCollectionId or updGenreId or updLength or updFilesize or updComposer:
                query = __getQueryUpdateMedia(schema)
                #Logger.info(query)
                cursor.execute(query, [year, collectionId, genreId, length, filesize, composer, oldMedia.getId()])
                database.commit()
                return MediaModel.ReturnType.UPDATED
            else:
                Logger.info(" - no need to update media")
                return MediaModel.ReturnType.NOT_UPDATED
        else:
            Logger.info("Error - MediaAccessor: media is None in updatemedia")
    except:
        Logger.info("Error: unable to update media")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    return MediaModel.ReturnType.ERROR

def updateAudio(database, cursor, schema, oldAudio, newAudio):
    """Updates the given oldAudio stored in the media database with the values of the given newAudio.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        oldAudio: An Audio object containing the valued of the old audio stored in the media database.
        newAudio: An Audio object containing the new values of the audio.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    try:
        if oldAudio != None and newAudio != None:
            Logger.info("update audio (" + str(oldAudio.getId()) + "):")
            artistId = None
            if oldAudio.getArtist() != None:
                artistId = oldAudio.getArtist().getId()
            lyrics = oldAudio.getLyrics()
            tracknumber = oldAudio.getTracknumber()
            discnumber = oldAudio.getDiscnumber()
            bitrate = oldAudio.getBitrate()
            bpm = oldAudio.getBPM()
            #check what to update and set right values
            updArtistId = False
            if newAudio.getArtist() != None and newAudio.getArtist().getId() != None and newAudio.getArtist().getId() != artistId:
                updArtistId = True
                Logger.info(" - change artistid to " + str(newAudio.getArtist().getId()))
                artistId = newAudio.getArtist().getId()
#            elif newAudio.getArtist() == None and artistId != None:
#                updArtistId = True
#                artistId = None
            updLyrics = oldAudio.getLyrics() != newAudio.getLyrics()
            if updLyrics:
                lyrics = newAudio.getLyrics()
                if oldAudio.getLyrics()!=None and newAudio.getLyrics()!=None:
                    Logger.info(" - lyrics from " + oldAudio.getLyrics() + " to " + newAudio.getLyrics())
                else:
                    Logger.info(" - lyrics -> one was None")
            updTracknumber = oldAudio.getTracknumber() != newAudio.getTracknumber()
            if updTracknumber:
                tracknumber = newAudio.getTracknumber()
                if oldAudio.getTracknumber()!=None and newAudio.getTracknumber()!=None:
                    Logger.info(" - tracknumber from " + str(oldAudio.getTracknumber()) + " to " + str(newAudio.getTracknumber()))
                else:
                    Logger.info(" - tracknumber -> one was None")
            updDiscnumber = oldAudio.getDiscnumber() != newAudio.getDiscnumber()
            if updDiscnumber:
                discnumber = newAudio.getDiscnumber()
                if oldAudio.getDiscnumber()!=None and newAudio.getDiscnumber()!=None:
                    Logger.info(" - discnumber from " + str(oldAudio.getDiscnumber()) + " to " + str(newAudio.getDiscnumber()))
                else:
                    Logger.info(" - discnumber -> one was None")
            updBitrate = oldAudio.getBitrate() != newAudio.getBitrate()
            if updBitrate:
                bitrate = newAudio.getBitrate()
                if oldAudio.getBitrate()!=None and newAudio.getBitrate()!=None:
                    Logger.info(" - bitrate from " + str(oldAudio.getBitrate()) + " to " + str(newAudio.getBitrate()))
                else:
                    Logger.info(" - bitrate -> one was None")
            updBPM = oldAudio.getBPM() != newAudio.getBPM()
            if updBPM:
                bpm = newAudio.getBPM()
                if oldAudio.getBPM()!=None and newAudio.getBPM()!=None:
                    Logger.info(" - bpm from " + str(oldAudio.getBPM()) + " to " + str(newAudio.getBPM()))
                else:
                    Logger.info(" - bpm -> one was None")
            if updArtistId or updBitrate or updBPM or updDiscnumber or updLyrics or updTracknumber:
                if artistId == None:
                    artistId = -1
                query = __getQueryUpdateAudio(schema)
                #Logger.info(query)
                cursor.execute(query, [artistId, lyrics, tracknumber, discnumber, bitrate, bpm, oldAudio.getId()])
                database.commit()
                return MediaModel.ReturnType.UPDATED
            else:
                Logger.info(" - no need to update audio")
                return MediaModel.ReturnType.NOT_UPDATED
        else:
            Logger.info("Error - MediaAccessor: audio is None in updateaudio")
    except:
        Logger.info("Error: unable to update audio")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    return MediaModel.ReturnType.ERROR

############
## DELETE ##
############
def deleteMedia(database, cursor, schema, mediaId):
    """Deletes from the media database the entry with the given id.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        mediaId: An integer representing the id of the entry to delete.
    
    Returns:
        True is returned if the deletion was successful, False otherwise.
    """
    deleted = False
    try:
        query = __getQueryDeleteMedia(schema)
        #Logger.info(query)
        cursor.execute(query, [mediaId])
        database.commit()
        deleted = True
    except:
        Logger.info("Error: unable to delete media: " + str(mediaId))
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.DELETE, traceback.format_exc())
    return deleted

def deleteCollection(database, cursor, schema, collectionId):
    """Deletes from the media database the collection with the given id.
    
    Args:
        database: A Connection object representing the database to be cleaned.
        cursor: A Cursor object needed for the execution of the database queries.
        schema: A String containing the name of the schema where the query should be executed.
        collectionId: An integer representing the id of the collection to delete.
    
    Returns:
        True is returned if the deletion was successful, False otherwise.
    """
    deleted = False
    try:
        query = __getQueryDeleteCollection(schema)
        #Logger.info(query)
        cursor.execute(query, [collectionId])
        database.commit()
        deleted = True
    except:
        Logger.info("Error: unable to delete collection: " + str(collectionId))
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.DELETE, traceback.format_exc())
    return deleted

####################
## SELECT_QUERIES ##
####################

def __getQuerySelectAllArtists(schema):
    """Creates a query for selecting all artists from the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_ARTIST
    return query

def __getQuerySelectAllGenres(schema):
    """Creates a query for selecting all genres from the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_GENRE
    return query

def __getQuerySelectAllAudios(schema, audioId=None):
    """Creates a query for selecting a specific or all audios from the media database, depending on the value of audioId.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        audioId: An integer value containing the id of the audio which should be selected.
            If None all audios are selected. Default None.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select a." + COL_AUDIO_ID + " as '" + COL_AUDIO_SELECT_ID + "', a." + COL_AUDIO_LYRICS
    query += ", a." + COL_AUDIO_TRACKNUMBER + ", a." + COL_AUDIO_DISCNUMBER + ", a." + COL_AUDIO_BITRATE + ", a." + COL_AUDIO_BPM
    query += ", m." + COL_MEDIA_ID + " as '" + COL_MEDIA_SELECT_ID + "', m." + COL_MEDIA_TYPE + ", m." + COL_MEDIA_TITLE
    query += ", m." + COL_MEDIA_YEAR + ", m." + COL_MEDIA_COMPOSER + ", m." + COL_MEDIA_LENGTH + ", m." + COL_MEDIA_FILETYPE
    query += ", m." + COL_MEDIA_FILESIZE + ", ar." + COL_ART_ID + " as '" + COL_ART_SELECT_ID + "', ar." + COL_ART_NAME + " as '" + COL_ART_SELECT_NAME
    query += "', g." + COL_GENRE_ID + " as '" + COL_GENRE_SELECT_ID + "', g." + COL_GENRE_NAME + " as '" + COL_GENRE_SELECT_NAME
    query += "', c." + COL_COLLECTION_ID + " as '" + COL_COLLECTION_SELECT_ID + "', c." + COL_COLLECTION_NAME + " as '" + COL_COLLECTION_SELECT_NAME
    query += "' from " + schema + "." + TABLE_AUDIO
    query += " a join " + schema + "." + TABLE_MEDIA + " m on a." + COL_AUDIO_MEDIA_ID + " = m." + COL_MEDIA_ID
    query += " join " + schema + "." + TABLE_ARTIST + " ar on a." + COL_AUDIO_ARTIST_ID + " = ar." + COL_ART_ID
    query += " left join " + schema + "." + TABLE_GENRE + " g on m." + COL_MEDIA_GENRE_ID + " = g." + COL_GENRE_ID
    query += " left join " + schema + "." + TABLE_COLLECTION + " c on m." + COL_MEDIA_COLLECTION_ID + " = c." + COL_COLLECTION_ID
    if audioId != None:
        query += " where a." + COL_AUDIO_ID + " = " + str(audioId)
    return query

def __getQuerySelectAllRatingsForMediaId(schema, mediaId):
    """Creates a query for selecting all ratings for a given mediaId from the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        mediaId: An integer representing the id of the media for which the rating should be selected.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_RATING
    query += " where " + COL_RATING_MEDIA_ID + " = " + str(mediaId)
    return query

def __getQuerySelectAllRatingsForMediaIdAndUser(schema, mediaId):
    """Creates a query for selecting all ratings for a given mediaId and a given user from the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        mediaId: An integer representing the id of the media for which the rating should be selected.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select * from " + schema + "." + TABLE_RATING
    query += " where " + COL_RATING_MEDIA_ID + " = " + str(mediaId)
    query += " and " + COL_RATING_USERNAME + " = %s"
    return query

def __getQuerySelectAudiosForMediaIds(schema, mediaIds):
    """Creates a query for selecting all audios for some given mediaIds from the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        mediaIds: A list of integers representing the ids of the audios which should be selected.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select a." + COL_AUDIO_ID + " as '" + COL_AUDIO_SELECT_ID + "', a." + COL_AUDIO_LYRICS
    query += ", a." + COL_AUDIO_TRACKNUMBER + ", a." + COL_AUDIO_DISCNUMBER + ", a." + COL_AUDIO_BITRATE + ", a." + COL_AUDIO_BPM
    query += ", m." + COL_MEDIA_ID + " as '" + COL_MEDIA_SELECT_ID + "', m." + COL_MEDIA_TYPE + ", m." + COL_MEDIA_TITLE
    query += ", m." + COL_MEDIA_YEAR + ", m." + COL_MEDIA_COMPOSER + ", m." + COL_MEDIA_LENGTH + ", m." + COL_MEDIA_FILETYPE
    query += ", m." + COL_MEDIA_FILESIZE + ", ar." + COL_ART_ID + " as '" + COL_ART_SELECT_ID + "', ar." + COL_ART_NAME + " as '" + COL_ART_SELECT_NAME
    query += "', g." + COL_GENRE_ID + " as '" + COL_GENRE_SELECT_ID + "', g." + COL_GENRE_NAME + " as '" + COL_GENRE_SELECT_NAME
    query += "', c." + COL_COLLECTION_ID + " as '" + COL_COLLECTION_SELECT_ID + "', c." + COL_COLLECTION_NAME + " as '" + COL_COLLECTION_SELECT_NAME
    query += "' from " + schema + "." + TABLE_AUDIO
    query += " a join " + schema + "." + TABLE_MEDIA + " m on a." + COL_AUDIO_MEDIA_ID + " = m." + COL_MEDIA_ID
    query += " join " + schema + "." + TABLE_ARTIST + " ar on a." + COL_AUDIO_ARTIST_ID + " = ar." + COL_ART_ID
    query += " left join " + schema + "." + TABLE_GENRE + " g on m." + COL_MEDIA_GENRE_ID + " = g." + COL_GENRE_ID
    query += " left join " + schema + "." + TABLE_COLLECTION + " c on m." + COL_MEDIA_COLLECTION_ID + " = c." + COL_COLLECTION_ID
    if mediaIds != None and len(mediaIds) > 0:
        query += " where a." + COL_AUDIO_MEDIA_ID + " in ("
        for mediaId in mediaIds:
            query += str(mediaId) + ","
        query = query[0:len(query) - 1] + ")"
    return query
        

####################
## INSERT-QUERIES ##
####################

def __getQueryInsertArtist(schema):
    """Creates a query for inserting the given artist into the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_ARTIST
    query += "(" + COL_ART_NAME + ") values (%s)"
    return query

def __getQueryInsertCollection(schema):
    """Creates a query for inserting the given collection into the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "insert into " + schema + "." + TABLE_COLLECTION
    query += "(" + COL_COLLECTION_NAME + ") values (%s)"
    return query

def __getQueryInsertGenre(schema):
    """Creates a query for inserting the given genre into the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_GENRE
    query += "(" + COL_GENRE_NAME + ") values (%s)"
    return query

def __getQueryInsertRating(schema):
    """Creates a query for inserting the given rating into the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_RATING
    query += "(" + COL_RATING_MEDIA_ID + "," + COL_RATING_USERNAME + "," + COL_RATING_SCORE
    query += "," + COL_RATING_RATING + "," + COL_RATING_PLAYCOUNT + "," + COL_RATING_COMMENT + ") values ("
    query += "%s ,%s, %s, %s, %s, %s)"
    return query

def __getQueryInsertMedia(schema):
    """Creates a query for inserting the given media into the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_MEDIA
    query += "(" + COL_MEDIA_TYPE + "," + COL_MEDIA_TITLE + "," + COL_MEDIA_YEAR + "," + COL_MEDIA_COMPOSER + "," + COL_MEDIA_COLLECTION_ID
    query += "," + COL_MEDIA_GENRE_ID + "," + COL_MEDIA_LENGTH + "," + COL_MEDIA_FILESIZE + "," + COL_MEDIA_FILETYPE + ") values (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
    return query

def __getQueryInsertAudio(schema):
    """Creates a query for inserting the given audio into the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_AUDIO
    query += "(" + COL_AUDIO_MEDIA_ID + "," + COL_AUDIO_ARTIST_ID + "," + COL_AUDIO_LYRICS
    query += "," + COL_AUDIO_TRACKNUMBER + "," + COL_AUDIO_DISCNUMBER + "," + COL_AUDIO_BITRATE + "," + COL_AUDIO_BPM + ") values ("
    query += "%s, %s, %s, %s, %s, %s, %s)"
    return query

####################
## UPDATE-QUERIES ##
####################

def __getQueryUpdateArtist(schema):
    """Creates a query for updating an artist of the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    #throws exception because name must not be null
    query = "update " + schema + "." + TABLE_ARTIST
    query += " set " + COL_ART_NAME + " = "
    query += "%s where " + COL_ART_ID + " = %s"
    return query

def __getQueryUpdateCollection(schema):
    """Creates a query for updating a collection of the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_COLLECTION
    query += " set " + COL_COLLECTION_NAME + " = "
    query += "%s where " + COL_COLLECTION_ID + " = %s"
    return query

def __getQueryUpdateGenre(schema):
    """Creates a query for updating a genre of the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_GENRE
    query += " set " + COL_GENRE_NAME + " = "
    query += "%s where " + COL_GENRE_ID + " = %s"
    return query

def __getQueryUpdateRating(schema):
    """Creates a query for updating a rating of the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_RATING
    query += " set " + COL_RATING_SCORE + " = "
    query += "%s, " + COL_RATING_RATING + " = "
    query += "%s, " + COL_RATING_PLAYCOUNT + " = "
    query += "%s, " + COL_RATING_COMMENT + " = "
    query += "%s where " + COL_RATING_ID + " = %s"
    return query

def __getQueryUpdateMedia(schema):
    """Creates a query for updating a media of the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_MEDIA
    query += " set " + COL_MEDIA_YEAR + " = "
    query += "%s, " + COL_MEDIA_COLLECTION_ID + " = "
    query += "%s, " + COL_MEDIA_GENRE_ID + " = "
    query += "%s, " + COL_MEDIA_LENGTH + " = "
    query += "%s, " + COL_MEDIA_FILESIZE + " = "
    query += "%s, " + COL_MEDIA_COMPOSER + " = "
    query += "%s where " + COL_MEDIA_ID + " = %s"
    return query

def __getQueryUpdateAudio(schema):
    """Creates a query for updating an audio of the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_AUDIO
    query += " set " + COL_AUDIO_ARTIST_ID + " = "
    query += "%s, " + COL_AUDIO_LYRICS + " = "
    query += "%s, " + COL_AUDIO_TRACKNUMBER + " = "
    query += "%s, " + COL_AUDIO_DISCNUMBER + " = "
    query += "%s, " + COL_AUDIO_BITRATE + " = "
    query += "%s, " + COL_AUDIO_BPM + " = "
    query += "%s where " + COL_AUDIO_ID + " = %s"
    return query

####################
## DELETE-QUERIES ##
####################

def __getQueryDeleteMedia(schema):
    """Creates a query for deleting a media of the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "delete from " + schema + "." + TABLE_MEDIA + " where " + COL_MEDIA_ID + " = %s"
    return query

def __getQueryDeleteCollection(schema):
    """Creates a query for deleting a collection of the media database.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "delete from " + schema + "." + TABLE_COLLECTION + " where " + COL_COLLECTION_ID + " = %s"
    return query
    

